-- 京津冀机器人推送
with network_detail as (
    select virt_code,
           virt_name,
           provider_id,
           provider_desc,
           agent_code,
           agent_name,
           city_id,
           city_desc,
           code,
           name,
           zone_id,
           zone_code,
           zone_name
    from jms_dim.dim_network_whole_massage
    where is_delete=1
      and is_enable=1
      and network_type=6
),
terminal_sign_punctuality_rate as(
select
 final_sign_network_code --最终签收派件网点编码
,max(final_sign_network_name) as final_sign_network_name --网点
,sum(need_sign_count) as need_sign_sum --应签收汇总
,sum(all_sign_22) as       sign_sum
 ,round(sum(all_sign_22) /sum(need_sign_count),4) as  aging_agin_rate
 ,(sum(need_sign_count)-sum(all_sign_22)) as  unsign_sum
 ,sum(all_sign_24) as all_sign_24  --
 -- ,round(sum(all_sign_24) /sum(need_sign_count),4) as rate_24
 ,dt as date_time
 ,dt
from jms_dm.dm_terminal_sign_punctuality_rate_dt
where dt='{{ execution_date | cst_ds }}'
group by dt,final_sign_network_code
    ),
    taking_union_sign as(
    select dt,
           network_code,
           max(network_name) as network_name,
           sum(scan_sum)     as active_sign_sum
    from jms_dm.dm_network_taking_union_sign_hour_sum_dt
    where dt = '{{ execution_date | cst_ds }}'
      and type1 = '实际签收量'
    group by dt, network_code
    ),
       taking_union_sign2 as(
        select dt,
               network_code,
               max(network_name) as network_name,
               sum(scan_sum)     as day_aging_sign_sum
        from jms_dm.dm_network_taking_union_sign_hour_sum_dt
        where dt = '{{ execution_date | cst_ds }}'
          and type1 = '时效签收量'
        group by dt, network_code
        )
insert overwrite table jms_dm.dm_network_jjj_aging_sign_detail_dt partition (dt)
  select
          a.virt_code   -- 虚拟代理区
         ,a.virt_name   -- 虚拟代理区
         ,a.provider_id  --省份id
         ,a.provider_desc  --省份
         ,b.final_sign_network_code  -- 网点code
         ,b.final_sign_network_name   --网点
         ,b.need_sign_sum             -- 应派件量
         ,b.sign_sum                -- 时效签收量
         ,b.aging_agin_rate         -- 时效签收率
         ,b.unsign_sum              --未签收量
         ,b.date_time              --业务日期
         ,a.city_id         --城市id
         ,a.city_desc       --城市
         ,a.zone_id   as  district_id      --区域(片区)编码'
         ,a.zone_name  as district_desc       -- 区域(片区)名称
         ,b.all_sign_24   --24点前时效签收量
         ,d.active_sign_sum   --当日实际签收量
         ,e.day_aging_sign_sum  --当日时效签收量
         ,a.agent_code
         ,a.agent_name
         ,b.dt
from network_detail a
join terminal_sign_punctuality_rate b
    on a.code=b.final_sign_network_code
--     left join  (select * from jms_dim.dim_sys_agency_district_city_base
--      where is_enable=1
--        and is_delete=1
--     ) c
--       on a.city_id=c.city_id
      left join taking_union_sign d
      on a.code=d.network_code
   left join taking_union_sign2 e
   on a.code=e.network_code
distribute by 2;
